﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;
using System.IO;

namespace OPCService.Service
{
    class SQLiteService: IDisposable
    {
        private SQLiteConnection _connection;
        private string _databasePath;
        private bool _disposed = false;

        /// <summary>
        /// 初始化 SQLite 数据库连接
        /// </summary>
        /// <param name="databasePath">数据库文件路径</param>
        public SQLiteService(string databasePath)
        {
            _databasePath = databasePath;
            InitializeDatabase();
        }

        /// <summary>
        /// 初始化数据库（如果不存在则创建）
        /// </summary>
        private void InitializeDatabase()
        {
            // 如果数据库文件不存在则创建
            if (!File.Exists(_databasePath))
            {
                SQLiteConnection.CreateFile(_databasePath);
            }

            // 创建连接字符串
            string connectionString = $"Data Source={_databasePath};Version=3;Pooling=True;Max Pool Size=10;";

            // 创建连接
            _connection = new SQLiteConnection(connectionString);
            _connection.Open();
        }

        /// <summary>
        /// 执行非查询SQL命令（INSERT, UPDATE, DELETE）
        /// </summary>
        /// <param name="sql">SQL命令</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sql, Dictionary<string, object> parameters = null)
        {
            using (var command = CreateCommand(sql, parameters))
            {
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行查询并返回第一行第一列的值
        /// </summary>
        /// <param name="sql">SQL查询</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>查询结果</returns>
        public object ExecuteScalar(string sql, Dictionary<string, object> parameters = null)
        {
            using (var command = CreateCommand(sql, parameters))
            {
                return command.ExecuteScalar();
            }
        }

        /// <summary>
        /// 执行查询并返回DataTable
        /// </summary>
        /// <param name="sql">SQL查询</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>包含结果的DataTable</returns>
        public DataTable ExecuteQuery(string sql, Dictionary<string, object> parameters = null)
        {
            using (var command = CreateCommand(sql, parameters))
            {
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
                {
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
        }

        /// <summary>
        /// 执行事务操作
        /// </summary>
        /// <param name="actions">要在事务中执行的操作列表</param>
        public void ExecuteTransaction(List<Action<SQLiteCommand>> actions)
        {
            using (var transaction = _connection.BeginTransaction())
            {
                try
                {
                    using (var command = new SQLiteCommand(_connection))
                    {
                        command.Transaction = transaction;

                        foreach (var action in actions)
                        {
                            action.Invoke(command);
                        }
                    }

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        /// <summary>
        /// 创建带参数的SQLite命令
        /// </summary>
        private SQLiteCommand CreateCommand(string sql, Dictionary<string, object> parameters)
        {
            var command = new SQLiteCommand(sql, _connection);

            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
                }
            }

            return command;
        }

        /// <summary>
        /// 检查表是否存在
        /// </summary>
        public bool TableExists(string tableName)
        {
            string sql = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name=@name";
            var parameters = new Dictionary<string, object> { { "@name", tableName } };

            long count = (long)ExecuteScalar(sql, parameters);
            return count > 0;
        }

        /// <summary>
        /// 创建表（如果不存在）
        /// </summary>
        public void CreateTableIfNotExists(string tableName, string tableDefinition)
        {
            if (!TableExists(tableName))
            {
                string sql = $"CREATE TABLE {tableName} ({tableDefinition})";
                ExecuteNonQuery(sql);
            }
        }

        /// <summary>
        /// 插入数据并返回最后插入的行ID
        /// </summary>
        public long Insert(string tableName, Dictionary<string, object> data)
        {
            // 构建参数化插入语句
            var columns = string.Join(", ", data.Keys);
            var values = string.Join(", ", data.Keys.Select(k => "@" + k));

            string sql = $"INSERT INTO {tableName} ({columns}) VALUES ({values}); SELECT last_insert_rowid();";

            using (var command = CreateCommand(sql, data))
            {
                return (long)command.ExecuteScalar();
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        public int Update(string tableName, Dictionary<string, object> data, string whereClause, Dictionary<string, object> whereParams = null)
        {
            // 构建SET子句
            var setClauses = data.Keys.Select(k => $"{k} = @{k}");
            string setClause = string.Join(", ", setClauses);

            // 合并参数
            var parameters = new Dictionary<string, object>(data);
            if (whereParams != null)
            {
                foreach (var param in whereParams)
                {
                    parameters.Add(param.Key, param.Value);
                }
            }

            string sql = $"UPDATE {tableName} SET {setClause} WHERE {whereClause}";

            return ExecuteNonQuery(sql, parameters);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        public int Delete(string tableName, string whereClause, Dictionary<string, object> parameters = null)
        {
            string sql = $"DELETE FROM {tableName} WHERE {whereClause}";
            return ExecuteNonQuery(sql, parameters);
        }

        /// <summary>
        /// 备份数据库到指定路径
        /// </summary>
        public void BackupDatabase(string backupPath)
        {
            using (var source = new SQLiteConnection(_connection.ConnectionString))
            using (var destination = new SQLiteConnection($"Data Source={backupPath};Version=3;"))
            {
                source.Open();
                destination.Open();
                source.BackupDatabase(destination, "main", "main", -1, null, 0);
            }
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    if (_connection != null)
                    {
                        if (_connection.State != ConnectionState.Closed)
                        {
                            _connection.Close();
                        }
                        _connection.Dispose();
                    }
                }
                _disposed = true;
            }
        }

        ~SQLiteService()
        {
            Dispose(false);
        }
    }
}
